package models

import (
	"Hypnos-Soundcore/components/feishu"
	"Hypnos-Soundcore/constants"
	"errors"
	"fmt"
	"math/rand"
	"strconv"
	"strings"
	"time"

	"github.com/beego/beego/v2/client/orm"
	"github.com/xuri/excelize/v2"
	"log"
	"os"
	"path/filepath"
)

// DidInfoBase .
type DidInfoBase struct {
	P2PDid      string `orm:"column(p2p_did)" json:"p2p_did" swaggo:"true,基站DID号"`
	PushDid     string `orm:"column(push_did)" json:"push_did" swaggo:"true,推送DID号"`
	P2PLicense  string `orm:"column(p2p_license)" json:"p2p_license" swaggo:"true,p2p许可证"`
	PushLicense string `orm:"column(push_license)" json:"push_license" swaggo:"true,push许可证"`
	NdtDid      string `orm:"column(ndt_did)" json:"ndt_did" swaggo:"true,NDTDID号"`
	NdtLicense  string `orm:"column(ndt_license)" json:"ndt_license" swaggo:"true,p2p许可证"`
	WakeupFlag  int    `orm:"column(wakeup_flag)" json:"wakeup_flag" swaggo:"true,0-无唤醒码 1-有唤醒码"`
}

// DidInfoModel .
type DidInfoModel struct {
	DidID     int64  `orm:"column(did_id);pk;auto" json:"did_id"`
	StationSn string `orm:"column(station_sn)" json:"station_sn" swaggo:"true,基站SN"`
	DidInfoBase
	ServerID int64 `orm:"column(server_id)" json:"server_id"`
	BaseColumn
}

// TableName specified table name
func (*DidInfoModel) TableName() string {
	return constants.TableDidInfo
}

// PrefixOrderBase
type DidPrefixOrderBase struct {
	DidPrefix    string  `orm:"column(did_prefix)" json:"did_prefix" swaggo:"true,前缀码"`
	ServerId     int     `orm:"column(server_id)" json:"server_id" swaggo:"true,服务器ID"`
	WakeupFlag   int     `orm:"column(wakeup_flag)" json:"wakeup_flag" swaggo:"true,唤醒标识(0-无唤醒码 1-有唤醒码)"`
	DidUnitPrice float64 `orm:"column(did_unit_price)" json:"did_unit_price" swaggo:"true,did单价"`
	OrderNum     int     `orm:"column(order_num)" json:"order_num" swaggo:"true,下单数量"`
	CreateTime   int64   `orm:"column(create_time)" json:"create_time" swaggo:"true,创建时间"`
	UpdateTime   int64   `orm:"column(update_time)" json:"update_time" swaggo:"true,更新时间"`
	Status       int     `orm:"column(status)" json:"status" swaggo:"true,状态:0-删除,1-待审核 ，2-已审核，3-已完成，4-已核销"`
}

// DidOrderBase
type DidOrderBase struct {
	PrefixOrderId int64   `orm:"column(prefix_order_id)" json:"prefix_order_id" swaggo:"true,前缀订单表ID"`
	OrderNo       string  `orm:"column(order_no)" json:"order_no" swaggo:"true,尚云订单号"`
	DidPrefix     string  `orm:"column(did_prefix)" json:"did_prefix" swaggo:"true,前缀码"`
	DidUnitPrice  float64 `orm:"column(did_unit_price)" json:"did_unit_price" swaggo:"true,单价"`
	OrderNum      int     `orm:"column(order_num)" json:"order_num" swaggo:"true,下单数量"`
	CreateTime    int64   `orm:"column(create_time)" json:"create_time" swaggo:"true,创建时间"`
	UpdateTime    int64   `orm:"column(update_time)" json:"update_time" swaggo:"true,更新时间"`
	Status        int     `orm:"column(status)" json:"status" swaggo:"true,状态:0-删除,1-待审核 ，2-处理完成"`
}

// DidOrderModel
type DidOrderModel struct {
	Id int64 `orm:"column(id);pk;auto" json:"id"`
	DidOrderBase
}

// TableName specified table name
func (*DidOrderModel) TableName() string {
	return constants.TableDidOrder
}

// OriginalDidInfoBase
type OriginalDidInfoBase struct {
	PrefixOrderId int64  `orm:"column(prefix_order_id)" json:"prefix_order_id" swaggo:"true,前置订单表ID"`
	OrderId       int64  `orm:"column(order_id)" json:"did_order_id" swaggo:"true,订单表ID"`
	OrderNo       string `orm:"column(order_no)" json:"order_num" swaggo:"true,尚云订单号"`
	DidPrefix     string `orm:"column(did_prefix)" json:"did_prefix" swaggo:"true,前缀码"`
	P2pDid        string `orm:"column(p2p_did)" json:"p2p_did" swaggo:"true,Did"`
	P2pLicense    string `orm:"column(p2p_license)" json:"p2p_license" swaggo:"true,许可证"`
	CreateTime    int64  `orm:"column(create_time)" json:"create_time" swaggo:"true,创建时间"`
}

// OriginalDidInfoModel
type OriginalDidInfoModel struct {
	Id int64 `orm:"column(id);pk;auto" json:"id"`
	OriginalDidInfoBase
}

// TableName specified table name
func (*OriginalDidInfoModel) TableName() string {
	return constants.TableOriginalDidInfo
}

type DidPrefixList struct {
	Id         int64  `orm:"column(id)" json:"id"`
	WakeupFlag int    `orm:"column(wakeup_flag)" json:"wakeup_flag"`
	DidPrefix  string `orm:"column(did_prefix)" json:"did_prefix"`
	Number     int    `orm:"column(number)" json:"number"`
}

// DidTotalModel
type DidTotalModel struct {
	Id          int64  `orm:"column(id);pk;auto" json:"id"`
	StationSn   string `orm:"column(station_sn)" json:"station_sn" swaggo:"true,基站SN"`
	ServerId    int    `orm:"column(server_id)" json:"server_id" swaggo:"true,服务器ID"`
	P2PDid      string `orm:"column(p2p_did)" json:"p2p_did" swaggo:"true,基站DID号"`
	PushDid     string `orm:"column(push_did)" json:"push_did" swaggo:"true,推送DID号"`
	P2PLicense  string `orm:"column(p2p_license)" json:"p2p_license" swaggo:"true,p2p许可证"`
	PushLicense string `orm:"column(push_license)" json:"push_license" swaggo:"true,push许可证"`
	NdtDid      string `orm:"column(ndt_did)" json:"ndt_did" swaggo:"true,NDTDID号"`
	NdtLicense  string `orm:"column(ndt_license)" json:"ndt_license" swaggo:"true,p2p许可证"`
	WakeupFlag  int    `orm:"column(wakeup_flag)" json:"wakeup_flag" swaggo:"true,0-无唤醒码 1-有唤醒码"`
	Project     string `orm:"column(project_name)" json:"project_name" swaggo:"true,项目名称"`
	Region      string `orm:"column(region)" json:"region" swaggo:"true,地区"`
	IsImport    int    `orm:"column(is_import)" json:"is_import" swaggo:"true,是否导入"`
	P2pSet      int    `orm:"column(is_pin)" json:"is_pin" swaggo:"true,版本号"`
	BaseColumn
}

// TableName specified table name
func (*DidTotalModel) TableName() string {
	return constants.TableDidTotal
}

// DidConfModel
type DidConfModel struct {
	Id             int64   `orm:"column(id);pk;auto" json:"id"`
	Project        string  `orm:"column(project)" json:"project" swaggo:"true,项目名称"`
	Region         string  `orm:"column(region)" json:"region" swaggo:"true,地区"`
	IsCheckConf    int     `orm:"column(is_check_conf)" json:"is_check_conf" swaggo:"true,是否开启校验"`
	ServerId       int     `orm:"column(server_id)" json:"server_id" swaggo:"true,服务器ID"`
	WakeupFlag     int     `orm:"column(wakeup_flag)" json:"wakeup_flag" swaggo:"true,唤醒标识"`
	DidPrefix      string  `orm:"column(did_prefix)" json:"did_prefix" swaggo:"true,前缀码"`
	P2pSet         int     `orm:"column(p2p_set)" json:"p2p_set" swaggo:"true,第几套p2p服务"`
	DidUnitPrice   float64 `orm:"column(did_unit_price)" json:"did_unit_price" swaggo:"true,单价"`
	OrderNum       int     `orm:"column(order_num)" json:"order_num" swaggo:"true,订单数量"`
	DidNumLimit    int     `orm:"column(did_num_limit)" json:"did_num_limit" swaggo:"true,前缀码限制数量"`
	OrderThreshold int     `orm:"column(order_threshold)" json:"order_threshold" swaggo:"true,剩余数量下单"`
}

// TableName specified table name
func (*DidConfModel) TableName() string {
	return constants.TableDidConf
}

// DidPrefixOrderModel
type DidPrefixOrderModel struct {
	Id           int64   `orm:"column(id);pk;auto" json:"id"`
	DidConfId    int64   `orm:"column(did_conf_id)" json:"did_conf_id" swaggo:"true,前缀码配置表ID"`
	ServerId     int     `orm:"column(server_id)" json:"server_id" swaggo:"true,服务器ID"`
	WakeupFlag   int     `orm:"column(wakeup_flag)" json:"wakeup_flag" swaggo:"true,唤醒标识(0-无唤醒码 1-有唤醒码)"`
	DidPrefix    string  `orm:"column(did_prefix)" json:"did_prefix" swaggo:"true,前缀码"`
	P2pSet       int     `orm:"column(p2p_set)" json:"p2p_set" swaggo:"true,p2p第几套服务"`
	DidUnitPrice float64 `orm:"column(did_unit_price)" json:"did_unit_price" swaggo:"true,did单价"`
	OrderNum     int     `orm:"column(order_num)" json:"order_num" swaggo:"true,下单数量"`
	CreateTime   int64   `orm:"column(create_time)" json:"create_time" swaggo:"true,创建时间"`
	UpdateTime   int64   `orm:"column(update_time)" json:"update_time" swaggo:"true,更新时间"`
	Status       int     `orm:"column(status)" json:"status" swaggo:"true,状态:0-删除,1-待审核 ，2-已审核，3-已完成，4-已核销"`
}

// TableName specified table name
func (*DidPrefixOrderModel) TableName() string {
	return constants.TableDidPrefixOrder
}

type DidUsageModel struct {
	Id             int64   `orm:"column(id);pk;auto" json:"id"`
	DidConfId      int64   `orm:"column(did_conf_id)" json:"did_conf_id"`
	ServerId       int     `orm:"column(server_id)" json:"server_id"`
	WakeupFlag     int     `orm:"column(wakeup_flag)" json:"wakeup_flag"`
	DidPrefix      string  `orm:"column(did_prefix)" json:"did_prefix"`
	RemainAmount   int     `orm:"column(remain_amount)" json:"remain_amount"`
	TotalAmount    int     `orm:"column(total_amount)" json:"total_amount"`
	DidUnitPrice   float64 `orm:"column(did_unit_price)" json:"did_unit_price"` //单价
	OrderNum       int     `orm:"column(order_num)" json:"order_num"`           //下单数量
	OrderThreshold int     `orm:"column(order_threshold)" json:"did_threshold"` //剩余数量下单
	DidNumLimit    int     `orm:"column(did_num_limit)" json:"did_num_limit"`   //整体数量限制
	P2pSet         int     `orm:"column(p2p_set)" json:"p2p_set"`               //第几套p2p服务
}

type DidInfoList struct {
	PrefixOrderId int64  `orm:"column(prefix_order_id)"`
	Project       string `orm:"column(project)"`
	Region        string `orm:"column(region)"`
}

func init() {
	orm.RegisterModel(new(DidConfModel))
	orm.RegisterModel(new(DidUsageModel))
	orm.RegisterModel(new(DidPrefixOrderModel))
	orm.RegisterModel(new(DidOrderModel))
	orm.RegisterModel(new(OriginalDidInfoModel))
	orm.RegisterModel(new(DidInfoModel))
	orm.RegisterModel(new(DidTotalModel))
}

/*********************************************定时任务一：余量检测&预订单生成****************************************************/
/**
* GetDidPrefixOrders
* @Date:2024-06-27 15:00:35
* @Description: 定时任务：查询当前环境的DID剩余用量、对应使用量;
*   					生成预订单;
*						每天执行一次
* @param localOrm
* @return err
**/
func GetDidPrefixOrders(localOrm orm.Ormer) (err error) {
	//todo 1、查询DID配置表
	var didConfList []DidConfModel
	_, err = localOrm.QueryTable(constants.TableDidConf).Filter("is_check_conf", constants.IsCheckConfOpen).All(&didConfList)
	if err != nil {
		feishu.SendFeishuAlertErr(feishu.WebhookURL, err)
		fmt.Println("查询DID配置表失败")
		return err
	}
	for _, didConf := range didConfList {
		//todo 2、先判断是否生成预订单，随后判断预订单是否重复，最后判断预订单状态
		var prefixOrderList []DidPrefixOrderModel
		sql := "select id, status from t_did_prefix_order where did_conf_id = ? and(status = 1 or status= 2) "
		num, err := localOrm.Raw(sql, didConf.Id).QueryRows(&prefixOrderList)
		if err != nil {
			feishu.SendFeishuAlertErr(feishu.WebhookURL, err)
			fmt.Println("飞书告警：查询预订单状态失败")
			return err
		}
		if num == 0 {
			fmt.Println("飞书通知：尚未生成预订单")
		} else if num == 1 {
			if prefixOrderList[0].Status == constants.DidPrefixOrderAwaitReview {
				feishu.SendFeishuAlert(feishu.WebhookURL, feishu.MessageDidPurchase+"\n"+"预订单编号: "+strconv.FormatInt(prefixOrderList[0].Id, 10)+"\n"+feishu.MessageUnreviewedOrder)
				fmt.Println("飞书告警：此条预订单已存在，当前状态待审核！")
				continue
			}
			if prefixOrderList[0].Status == constants.DidPrefixOrderReviewed {
				fmt.Println("飞书通知：此条预订单处理中，当前状态已审核！")
				continue
			}
		} else {
			feishu.SendFeishuAlert(feishu.WebhookURL, feishu.MessageDidPurchase+"\n"+"DID配置编号: "+strconv.FormatInt(didConf.Id, 10)+"\n"+feishu.MessageDidConfRepeat)
			fmt.Println("飞书告警：此条DID配置重复！")
			for _, prefixOrder := range prefixOrderList {
				feishu.SendFeishuAlert(feishu.WebhookURL, feishu.MessageDidPurchase+"\n"+"预订单编号: "+strconv.FormatInt(prefixOrder.Id, 10)+"\n"+feishu.MessagePrefixOrderRepeat)
				fmt.Println("飞书告警：此条预订单重复！")
			}
			continue
		}
		//todo 3、根据item.prefix查询t_did_info表，获取剩余量
		var remainAmount int
		length := len(didConf.DidPrefix)
		sql = fmt.Sprintf("select count(*) as remain_amount from t_did_info where (station_sn is null or station_sn = '') AND left(p2p_did, %d) = ? AND server_id = ? AND wakeup_flag = ?", length)
		//获取当前项目的数据库实例
		project := strings.Split(didConf.Project, "-")[0]
		currDB := orm.NewOrmUsingDB(project)
		err = currDB.Raw(sql, didConf.DidPrefix, didConf.ServerId, didConf.WakeupFlag).QueryRow(&remainAmount)
		if err != nil {
			feishu.SendFeishuAlertErr(feishu.WebhookURL, err)
			fmt.Println("飞书告警：查询余量失败!")
			return err
		}

		// todo 先判断余量逻辑，下单，不下单直接退出continue
		//todo 4、查询item.prefix查询t_did_info表,获取对应使用量
		var useAmount int
		sql = fmt.Sprintf("select count(*) as use_amount from t_did_info where (station_sn is not null or station_sn != '') AND left(p2p_did, %d) = ? AND server_id = ? AND wakeup_flag = ?", length)
		err = currDB.Raw(sql, didConf.DidPrefix, didConf.ServerId, didConf.WakeupFlag).QueryRow(&useAmount)
		if err != nil {
			feishu.SendFeishuAlertErr(feishu.WebhookURL, err)
			fmt.Println("飞书告警：查询使用量失败!")
		}
		//todo 5、将查询剩余量、余量、didConf配置项写入didUsage中
		var didUsage DidUsageModel
		didUsage.ServerId = didConf.ServerId
		didUsage.WakeupFlag = didConf.WakeupFlag
		didUsage.DidPrefix = didConf.DidPrefix
		didUsage.RemainAmount = remainAmount
		didUsage.TotalAmount = remainAmount + useAmount //did_num_limit 根据总限制数，分配具体的限制数量
		didUsage.DidConfId = didConf.Id
		didUsage.OrderNum = didConf.OrderNum
		didUsage.DidUnitPrice = didConf.DidUnitPrice
		didUsage.DidNumLimit = didConf.DidNumLimit
		didUsage.OrderThreshold = didConf.OrderThreshold
		didUsage.P2pSet = didConf.P2pSet

		//todo 6.1、判断是否下单
		if didUsage.RemainAmount < didUsage.OrderThreshold {
			//todo 6.2、判断是否需要新的前缀码
			if didUsage.TotalAmount+didUsage.OrderNum > didUsage.DidNumLimit {
				// todo 6.3、判断是否先处理余量，再处理超出部分
				if didUsage.TotalAmount < didUsage.DidNumLimit {
					prefixOrderNum := didUsage.DidNumLimit - didUsage.TotalAmount
					id, err := InsertPrefixOrder(localOrm, didUsage, prefixOrderNum)
					if err != nil {
						feishu.SendFeishuAlertErr(feishu.WebhookURL, err)
						fmt.Println("飞书告警：未超出限制部分，生成预订单失败")
						return err
					}
					exceedOrderNum := didUsage.OrderNum - prefixOrderNum
					// 告警内容：时间、当前总量、订单数量、限制数量、准备下单数量、新的前缀码
					feishu.SendFeishuAlert(feishu.WebhookURL, feishu.MessageDidPurchase+"\n"+
						feishu.MessageExceedPrefixLimit+"\n"+
						"预订单编号："+strconv.FormatInt(id, 10)+"\n"+
						"DID配置编号: "+strconv.FormatInt(didConf.Id, 10)+"\n"+
						"限制数量："+strconv.Itoa(didUsage.DidNumLimit)+"\n"+
						"当前总量："+strconv.Itoa(didUsage.TotalAmount)+"\n"+
						"原订单数量："+strconv.Itoa(didUsage.OrderNum)+"\n"+
						"已下单数量："+strconv.Itoa(prefixOrderNum)+"\n"+
						"新前缀准备下单数量："+strconv.Itoa(exceedOrderNum))
					fmt.Println("飞书告警：超出限制数量的部分，需要新的前缀码!")
				} else {
					feishu.SendFeishuAlert(feishu.WebhookURL, feishu.MessageDidPurchase+"\n"+
						feishu.MessageTotalEqualPrefixLimit+"\n"+
						"DID配置编号: "+strconv.FormatInt(didConf.Id, 10)+"\n"+
						"限制数量："+strconv.Itoa(didUsage.DidNumLimit)+"\n"+
						"当前总量："+strconv.Itoa(didUsage.TotalAmount)+"\n"+
						"订单数量："+strconv.Itoa(didUsage.OrderNum)+"\n"+
						"准备下单数量："+strconv.Itoa(didUsage.OrderNum))
					fmt.Println("飞书告警：总量等于限制数量，需要新的前缀码!")
				}
			} else {
				// todo 7、无需新的前缀码，直接下单
				prefixOrderNum := didUsage.OrderNum
				id, err := InsertPrefixOrder(localOrm, didUsage, prefixOrderNum)
				if err != nil {
					feishu.SendFeishuAlertErr(feishu.WebhookURL, err)
					fmt.Println("飞书告警：本轮DID购买任务：生成预订单失败")
					return err
				} else {
					feishu.SendFeishuInfo(feishu.WebhookURL, feishu.MessageDidPurchase+"\n"+
						"预订单编号: "+strconv.FormatInt(id, 10)+"\n"+
						"DID配置编号: "+strconv.FormatInt(didConf.Id, 10)+"\n"+
						"此条预订单需要运营人员审核！")
					fmt.Println("飞书通知：" + strconv.FormatInt(id, 10) + " 此条预订单需要运营人员审核！")
				}
			}
		} else {
			fmt.Println("飞书通知：DID充足，无需下单")
		}
	}
	return nil
}

// 插入预订单表
func InsertPrefixOrder(localOrm orm.Ormer, item DidUsageModel, prefixOrderNum int) (int64, error) {
	remainPrefixOrder := DidPrefixOrderModel{
		DidConfId:    item.DidConfId,
		P2pSet:       item.P2pSet,
		CreateTime:   time.Now().Unix(),
		UpdateTime:   time.Now().Unix(),
		DidUnitPrice: item.DidUnitPrice,
		OrderNum:     prefixOrderNum,
		ServerId:     item.ServerId,
		WakeupFlag:   item.WakeupFlag,
		DidPrefix:    item.DidPrefix,
		Status:       constants.DidPrefixOrderAwaitReview,
	}
	id, err := localOrm.Insert(&remainPrefixOrder)
	if err != nil {
		fmt.Println("插入预订单失败")
		return 0, err
	}
	return id, nil
}

/*********************************************定时任务二：安全校验&DID导入****************************************************/
/**
* ImportDidTotal
* @Date:2024-06-28 10:16:32
* @Description: 定时任务：安全校验；
						导入t_did_total表，导入完成后
* 						修改t_did_prefix_order表status=2为3，
*						每天执行一次
* @param o
* @return err
**/
func ImportDidTotal(o orm.Ormer) (err error) {
	//todo 1、查询预订单中已审核订单
	var prefixOrderList []DidPrefixOrderModel
	_, err = o.QueryTable(constants.TableDidPrefixOrder).Filter("status", constants.DidPrefixOrderReviewed).All(&prefixOrderList)
	if err != nil {
		feishu.SendFeishuAlertErr(feishu.WebhookURL, err)
		fmt.Println("查询已审核预订单表失败")
	}
	//todo 2、判断是否存在已审核的预订单
	if len(prefixOrderList) == 0 {
		//feishu.SendFeishuInfo(feishu.WebhookURL, feishu.MessageDidImport+"\n"+feishu.MessageReviewedPrefixOrderNotExist)
		fmt.Println("无已审核预订单,无需导入DID信息")
		return nil
	}
	for _, item := range prefixOrderList {
		tx, err := o.Begin()
		// todo 3、判断此条订单尚云方处理状态（待审核/已审核/处理完成）
		var didOrder DidOrderModel
		sql := `select id, did_prefix, order_num, status from ` + constants.TableDidOrder + ` where prefix_order_id = ?`
		err = tx.Raw(sql, item.Id).QueryRow(&didOrder)
		if err != nil {
			if err == orm.ErrNoRows {
				//feishu.SendFeishuInfo(feishu.WebhookURL, feishu.MessageDidImport+"\n"+"预订单编号: "+strconv.FormatInt(item.Id, 10)+"\n"+feishu.MessagePrefixOrderUnimport)
				fmt.Println(feishu.MessageDidImport + "\n" + "预订单编号: " + strconv.FormatInt(item.Id, 10) + "\n" + "此条订单尚云未写入!")
				continue
			} else {
				feishu.SendFeishuAlertErr(feishu.WebhookURL, err)
				fmt.Println("查询订单失败")
				return err
			}
		}
		if didOrder.Status == constants.DidOrderUnreviewed {
			fmt.Println(feishu.MessageDidImport + "\n" +
				"订单编号: " + strconv.FormatInt(didOrder.Id, 10) + "\n" +
				"预订单编号: " + strconv.FormatInt(item.Id, 10) + "\n" +
				"订单数量：" + strconv.Itoa(didOrder.OrderNum) + "\n" +
				"此条订单尚云方未审核!")
			continue
		}
		if didOrder.Status == constants.DidOrderProcessing {
			fmt.Println(feishu.MessageDidImport + "\n" +
				"订单编号: " + strconv.FormatInt(didOrder.Id, 10) + "\n" +
				"预订单编号: " + strconv.FormatInt(item.Id, 10) + "\n" +
				"订单数量：" + strconv.Itoa(didOrder.OrderNum) + "\n" +
				"此条订单尚云方处理中!")
			continue
		}
		// todo 4、安全验证一：判断尚云写入订单表中的的DID信息{前缀码、数量}是否一致
		if item.DidPrefix != didOrder.DidPrefix || item.OrderNum != didOrder.OrderNum {
			feishu.SendFeishuAlert(feishu.WebhookURL, feishu.MessageDidImport+"\n"+
				"订单编号: "+strconv.FormatInt(didOrder.Id, 10)+"\n"+
				"预订单编号: "+strconv.FormatInt(item.Id, 10)+"\n"+
				"订单前缀码："+didOrder.DidPrefix+"--"+"订单数量："+strconv.Itoa(didOrder.OrderNum)+"\n"+
				"预订单前缀码："+item.DidPrefix+"--"+"预订单数量："+strconv.Itoa(item.OrderNum)+"\n"+
				feishu.MessageOrderMismatchPrefixOrder)
			fmt.Println(feishu.MessageDidImport + "\n" + "预订单编号: " + strconv.FormatInt(item.Id, 10) + "预订单编号: " + strconv.FormatInt(item.Id, 10) + "此条订单尚云写入信息与预订单不一致!")
			continue
		}
		// todo 分批查询，设置batchSize，如1000
		var batchOriginalDid []OriginalDidInfoModel
		var originalDidList []OriginalDidInfoModel
		offset := constants.Offset
		for {
			// 分页查询 SQL 语句
			sql := `SELECT prefix_order_id, order_id, order_no, did_prefix, p2p_did, p2p_license FROM ` + constants.TableOriginalDidInfo + ` WHERE prefix_order_id = ? LIMIT ? OFFSET ?`
			num, err := tx.Raw(sql, item.Id, constants.BatchSize, offset).QueryRows(&batchOriginalDid)
			if err != nil {
				feishu.SendFeishuAlertErr(feishu.WebhookURL, err)
				fmt.Println("查询原始DID表失败！")
				return err
			}
			if num == 0 {
				break
			}
			//将每次查询结果都保存在originalDidList中
			originalDidList = append(originalDidList, batchOriginalDid...)

			// 如果查询结果小于 batchSize，说明已经查询完毕
			if len(batchOriginalDid) < constants.BatchSize {
				break
			}
			// 更新 offset 以获取下一个批次的数据
			offset += constants.BatchSize
		}
		//todo 5、安全验证二：判断尚云是否写入原始DID表中
		if len(originalDidList) == 0 {
			feishu.SendFeishuAlert(feishu.WebhookURL, feishu.MessageDidImport+"\n"+"预订单编号: "+strconv.FormatInt(item.Id, 10)+"\n"+feishu.MessageOriginalOrderNotExist)
			fmt.Println(feishu.MessageDidImport + "\n" + "预订单编号: " + strconv.FormatInt(item.Id, 10) + "此条预订单尚云未写入原始DID信息表中!")
			continue
		}
		// todo 6、安全验证三：判断写入原始表的DID数量和预订单表中DID数量是否一致
		if len(originalDidList) != item.OrderNum {
			feishu.SendFeishuAlert(feishu.WebhookURL, feishu.MessageDidImport+"\n"+
				"预订单编号: "+strconv.FormatInt(item.Id, 10)+"\n"+
				"尚云数量："+strconv.Itoa(len(originalDidList))+"\n"+
				"预订单数量："+strconv.Itoa(item.OrderNum)+"\n"+
				feishu.MessageOriginalOrderMismatchPrefixOrder)
			fmt.Println(feishu.MessageDidImport + "\n" + "预订单编号: " + strconv.FormatInt(item.Id, 10) + "此条预订单尚云导入的原始DID数量与预订单不一致!")
			continue
		}
		//todo 6、查询此条预订单对应的DID配置表中的项目名称和地区
		var project, region string
		sql = `select project, region from ` + constants.TableDidConf + ` where id = ?`
		err = tx.Raw(sql, item.DidConfId).QueryRow(&project, &region)
		if err != nil {
			feishu.SendFeishuAlertErr(feishu.WebhookURL, err)
			fmt.Println("查询项目名称和地区失败")
			return err
		}
		//todo 7、将此条订单对应的原始DID表中数据插入到t_did_total表中
		var didTotalList []DidTotalModel
		for _, original := range originalDidList {
			//todo 8、导入前，判断每一条记录的前缀码与预订单是否一致
			if item.DidPrefix != original.DidPrefix {
				feishu.SendFeishuAlert(feishu.WebhookURL, feishu.MessageDidImport+"\n"+
					"预订单编号: "+strconv.FormatInt(item.Id, 10)+"\n"+
					"尚云前缀码："+original.DidPrefix+"\n"+
					"预订单前缀码："+item.DidPrefix+"\n"+
					feishu.MessageOriginalOrderMismatchPrefixOrder)
				fmt.Println(feishu.MessageDidImport + "\n" + "预订单编号: " + strconv.FormatInt(item.Id, 10) + "此条尚云导入的原始DID前缀与预订单不一致!")
				break
			}
			didTotal := DidTotalModel{
				Project:     project, //项目名称security-qa
				Region:      region,  //地区
				StationSn:   "",      //为空
				ServerId:    item.ServerId,
				P2PDid:      original.P2pDid,
				PushDid:     original.P2pDid, // 与P2PDid一致
				P2PLicense:  original.P2pLicense,
				PushLicense: "",              //为空
				NdtDid:      original.P2pDid, // 与P2PDid一致
				NdtLicense:  "",              //为空
				WakeupFlag:  item.WakeupFlag,
				IsImport:    constants.DidUnimported, //默认未导入0，DB导入到t_did_info表后，修改为已导入1
				P2pSet:      item.P2pSet,
				BaseColumn: BaseColumn{
					CreateTime: int(time.Now().Unix()),
					UpdateTime: int(time.Now().Unix()),
					Status:     constants.DidUnimported,
				},
			}
			didTotalList = append(didTotalList, didTotal)
		}
		// todo 分批导入
		for i := 0; i < len(didTotalList); i += constants.ImportSize {
			end := i + constants.ImportSize
			if end > len(didTotalList) {
				end = len(didTotalList)
			}
			_, err = tx.InsertMulti(end-i, didTotalList[i:end])
			if err != nil {
				feishu.SendFeishuAlertErr(feishu.WebhookURL, err)
				fmt.Println("插入DID总表失败！")
				tx.Rollback()
				return err
			}
		}
		//todo 10、修改t_did_prefix_order表status=2为3，表示已完成，update_time=time.now()表示完成时间
		sqlPrefixOrder := `update ` + constants.TableDidPrefixOrder + ` set status= ?, update_time = ? where id = ?`
		_, err = tx.Raw(sqlPrefixOrder, constants.DidPrefixOrderCompleted, time.Now().Unix(), item.Id).Exec()
		if err != nil {
			feishu.SendFeishuAlertErr(feishu.WebhookURL, err)
			fmt.Println("修改预订单状态失败!")
			tx.Rollback()
			return err
		}
		//提交事务
		tx.Commit()
	}

	return nil
}

/*********************************************定时任务三：尚云方DID生成订单及DID信息写入****************************************************/
/**
* WriteOriginalDid
* @Date:2024-08-16 16:34:18
* @Description: 订单生成&写入原始DID信息表（尚云方），此逻辑为功能测试使用，真实环境不使用。
				此测试逻辑，忽略尚云方人员审核节点，直接生成订单，写入原始DID信息表。
				此测试逻辑，所生成p2p_did值，忽略字段唯一性约束。
* 尚云方定时任务流程如下：
1、尚云从t_did_prefix_oprder中读取status=2的订单；
2、将订单信息写入到t_did_order中，status=1(待审核)；
3、审核通过后，设置订单表中status=2（已审核），生成DID信息，写入到t_original_did_info表中；
4、写入完成后，设置订单表中status=3（已完成）
* @param o
* @return err
**/
func WriteOriginalDid(o orm.Ormer) (err error) {
	//todo 1、查询预订单中已审核的订单
	var prefixOrderList []DidPrefixOrderModel
	var didOrderList []DidOrderModel
	_, err = o.QueryTable(constants.TableDidPrefixOrder).Filter("status", constants.DidPrefixOrderReviewed).All(&prefixOrderList)
	if err != nil {
		feishu.SendFeishuAlertErr(feishu.WebhookURL, err)
		fmt.Println("查询已审核预订单表失败")
	}
	if len(prefixOrderList) == 0 {
		fmt.Println("无已审核预订单")
		return nil
	}

	//todo 2、根据预订单表中的订单信息生成订单信息，写入到t_did_order中
	for _, item := range prefixOrderList {
		order := DidOrderModel{
			DidOrderBase: DidOrderBase{
				PrefixOrderId: item.Id,
				OrderNo:       "Anker" + time.Now().Format("2006-0102-1504"),
				DidPrefix:     item.DidPrefix,
				DidUnitPrice:  item.DidUnitPrice,
				OrderNum:      item.OrderNum,
				CreateTime:    time.Now().Unix(),
				UpdateTime:    time.Now().Unix(),
				Status:        constants.DidOrderUnreviewed,
			},
		}
		id, err := o.Insert(&order)
		if err != nil {
			feishu.SendFeishuAlertErr(feishu.WebhookURL, err)
			fmt.Println("插入订单表失败")
			return err
		} else {
			order.Id = id
			didOrderList = append(didOrderList, order)
			feishu.SendFeishuInfo(feishu.WebhookURL, feishu.MessageDidPurchase+"\n"+"订单编号: "+strconv.FormatInt(order.Id, 10)+"\n"+"此条订单需要尚云人员审核！")
		}
	}
	//todo 3、根据订单表中OrderNum,生成记录插入原始DID信息表
	for _, order := range didOrderList {
		var originalDidList []OriginalDidInfoModel
		for i := 0; i < order.OrderNum; i++ {
			originalDid := OriginalDidInfoModel{
				OriginalDidInfoBase: OriginalDidInfoBase{
					PrefixOrderId: order.PrefixOrderId,
					OrderId:       order.Id,
					OrderNo:       order.OrderNo,
					DidPrefix:     order.DidPrefix,
					P2pDid:        order.DidPrefix + "anker-" + strconv.Itoa(i) + generateRandomString(),
					P2pLicense:    "license" + strconv.Itoa(i),
					CreateTime:    time.Now().Unix(),
				},
			}
			originalDidList = append(originalDidList, originalDid)
		}

		//分批插入
		for i := 0; i < len(originalDidList); i += constants.BatchSize {
			end := i + constants.BatchSize
			if end > len(originalDidList) {
				end = len(originalDidList)
			}
			_, err = o.InsertMulti(end-i, originalDidList[i:end])
			if err != nil {
				feishu.SendFeishuAlertErr(feishu.WebhookURL, err)
				fmt.Println("插入原始DID信息表失败")
				return err
			}
		}
		// 订单表状态改为已完成
		sql := `update ` + constants.TableDidOrder + ` set status = ? where prefix_order_id = ?`
		_, err = o.Raw(sql, constants.DidOrderCompleted, order.PrefixOrderId).Exec()
		if err != nil {
			feishu.SendFeishuAlertErr(feishu.WebhookURL, err)
			fmt.Println("修改订单状态为已完成失败")
			return err
		}
	}
	return nil
}

// generateRandomString 生成一个随机的12位字符串
func generateRandomString() string {
	const charset = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789"
	const length = 12
	rand.Seed(time.Now().UnixNano())
	b := make([]byte, length)
	for i := range b {
		b[i] = charset[rand.Intn(len(charset))]
	}
	return string(b)
}

/*************************************************运营API接口********************************************************/

func GetPrefixOrderAndOrderList(o orm.Ormer, req RequestOrder) (res []ResponseOrder, err error) {
	var params []interface{}
	sql := `select a.id, a.did_prefix, a.order_num, a.status, b.status, a.create_time, a.update_time from t_did_prefix_order a left join t_did_order b on a.id = b.prefix_order_id where 1=1`
	if req.PrefixOrderID != 0 {
		sql += ` and a.id = ?`
		params = append(params, req.PrefixOrderID)
	}
	if req.PrefixOrderStatus != 0 {
		sql += ` and a.status = ?`
		params = append(params, req.PrefixOrderStatus)
	}
	if req.Year != "" {
		year, _ := strconv.Atoi(req.Year) // 转换年份为整数
		if req.Month != "" {
			month, _ := strconv.Atoi(req.Month) // 转换月份为整数
			// 使用正确的月份范围
			startTime := fmt.Sprintf("%04d-%02d-01 00:00", year, month)
			// 获取月份的最后一天
			loc, _ := time.LoadLocation("Local")
			firstOfNextMonth := time.Date(year, time.Month(month)+1, 1, 0, 0, 0, 0, loc)
			lastOfMonth := firstOfNextMonth.Add(-time.Second).Format("2006-01-02 15:04")
			sql += ` AND a.create_time BETWEEN ? AND ?`
			params = append(params, startTime, lastOfMonth)
		} else {
			// 查询整个年份
			startTime := fmt.Sprintf("%04d-01-01 00:00", year)
			endTime := fmt.Sprintf("%04d-12-31 23:59", year)
			sql += ` AND a.create_time BETWEEN ? AND ?`
			params = append(params, startTime, endTime)
		}
	} else {
		fmt.Println(errors.New("年份不能为空"))
		return
	}
	//执行查询
	_, err = o.Raw(sql, params...).QueryRows(&res)
	if err != nil {
		fmt.Println("GetDidPrefixOrderList err:", err)
	} else {
		fmt.Println("GetDidPrefixOrderList success")
	}
	return
}

// 1、查询未审核预订单
func GetUnreviewdOrders(o orm.Ormer) (res []DidPrefixOrderModel, err error) {
	_, err = o.QueryTable(constants.TableDidPrefixOrder).Filter("status", constants.DidPrefixOrderAwaitReview).All(&res)
	if err != nil {
		fmt.Println("GetUnreviewedPrefixOrders err:", err)
	} else {
		fmt.Println("GetUnreviewedPrefixOrders success")
	}
	return
}

// 2、更新未审核预订单状态
func UpdateUnreviewedOrders(o orm.Ormer) (num int64, err error) {
	num, err = o.QueryTable(constants.TableDidPrefixOrder).Filter("status", constants.DidPrefixOrderAwaitReview).Update(orm.Params{"status": constants.DidPrefixOrderReviewed})
	if err != nil {
		fmt.Println("UpdatePrefixOrderStatus err:", err)
	} else {
		fmt.Println("UpdatePrefixOrderStatus success")
	}
	return
}

// 3、查询未结算预订单
func GetUnsettledOrders(o orm.Ormer) (res []DidPrefixOrderModel, err error) {
	_, err = o.QueryTable(constants.TableDidPrefixOrder).Filter("status", constants.DidPrefixOrderCompleted).All(&res)
	if err != nil {
		fmt.Println("GetCompletedOrders err:", err)
	} else {
		fmt.Println("GetCompletedOrders success")
	}
	return
}

// 4、导出未结算预订单
func ExportUnsettledOrders(o orm.Ormer) (res []DidPrefixOrderModel, err error) {
	// 查询未结算的前置订单
	res, err = GetUnsettledOrders(o)
	// 创建Excel文件
	f := excelize.NewFile()
	// 创建一个工作表
	index, _ := f.NewSheet("Sheet1")

	// 设置标题行
	titles := []string{"PrefixOrderId", "DidPrefix", "ServerId", "WakeupFlag", "DidUnitPrice", "DidAmount", "CreateTime", "UpdateTime", "Status"}
	for i, title := range titles {
		cell := fmt.Sprintf("%s1", string(rune('A'+i)))
		f.SetCellValue("Sheet1", cell, title)
	}

	// 写入数据行
	for i, item := range res {
		f.SetCellValue("Sheet1", fmt.Sprintf("A%d", i+2), item.Id)
		f.SetCellValue("Sheet1", fmt.Sprintf("B%d", i+2), item.DidPrefix)
		f.SetCellValue("Sheet1", fmt.Sprintf("C%d", i+2), item.ServerId)
		f.SetCellValue("Sheet1", fmt.Sprintf("D%d", i+2), item.WakeupFlag)
		f.SetCellValue("Sheet1", fmt.Sprintf("E%d", i+2), item.DidUnitPrice)
		f.SetCellValue("Sheet1", fmt.Sprintf("F%d", i+2), item.OrderNum)
		f.SetCellValue("Sheet1", fmt.Sprintf("G%d", i+2), item.CreateTime)
		f.SetCellValue("Sheet1", fmt.Sprintf("H%d", i+2), item.UpdateTime)
		f.SetCellValue("Sheet1", fmt.Sprintf("I%d", i+2), item.Status)
	}

	// 设置工作簿的默认工作表
	f.SetActiveSheet(index)
	//获取用户的主目录
	homeDir, err := os.UserHomeDir()
	if err != nil {
		log.Fatal(err)
	}

	//获取当前日期
	currentDate := time.Now().Format(constants.CurrentTime)
	//获取文档目录
	documentDir := filepath.Join(homeDir, "Documents")
	fileName := fmt.Sprintf("%s-DidOrder.xlsx", currentDate)
	//指定保存路径和文件名称
	excelFilePath := filepath.Join(documentDir, fileName)

	// 根据指定路径保存文件
	if err := f.SaveAs(excelFilePath); err != nil {
		fmt.Println("ExportPrefixOrderByStatus err:", err)
		return nil, err
	}
	fmt.Println("ExportPrefixOrderByStatus success")
	return
}

// 5、更新未结算（已完成）预订单状态
func UpdateUnsettledOrders(o orm.Ormer) (num int64, err error) {
	num, err = o.QueryTable(constants.TableDidPrefixOrder).Filter("status", constants.DidPrefixOrderCompleted).Update(orm.Params{"status": constants.DidPrefixOrderSettled})
	if err != nil {
		fmt.Println("UpdatePrefixOrderStatus err:", err)
		return
	}
	fmt.Println("UpdatePrefixOrderStatus success")
	return
}

// 6、查询预订单列表
func GetPrefixOrderList(o orm.Ormer) (res []DidPrefixOrderModel, err error) {
	_, err = o.QueryTable(constants.TableDidPrefixOrder).All(&res)
	if err != nil {
		fmt.Println("GetDidPrefixOrderList err:", err)
	} else {
		fmt.Println("GetDidPrefixOrderList success")
	}
	return
}

// 7、查询订单列表
func GetDidOrderList(o orm.Ormer) (res []DidOrderModel, err error) {
	_, err = o.QueryTable(constants.TableDidOrder).All(&res)
	if err != nil {
		fmt.Println("GetDidOrderList err:", err)
	} else {
		fmt.Println("GetDidOrderList success")
	}
	return
}

// 8、查询t_did_conf配置信息
func GetDidConfList(o orm.Ormer) (res []DidConfModel, err error) {
	_, err = o.QueryTable(constants.TableDidConf).All(&res)
	if err != nil {
		fmt.Println("GetDidConf err:", err)
	} else {
		fmt.Println("GetDidConf success")
	}
	return
}

// 9、修改t_did_conf配置信息
func UpdateDidConfByDto(o orm.Ormer, req []RequestDidConf) (num int64, err error) {
	for _, item := range req {
		if n, err := o.Update(&item); err != nil {
			fmt.Println("UpdateDidConf err:", err)
			return num, err
		} else {
			num += n
		}
	}
	fmt.Println("UpdateDidConf success")
	return
}

// 10、新增t_did_conf配置信息
func InsertDidConfByDto(o orm.Ormer, req RequestDidConf) (int64, error) {
	//插入数据
	id, err := o.Insert(&req)
	if err != nil {
		fmt.Println("AddDidConf err:", err)
		return 0, err
	}
	fmt.Println("AddDidConf success")
	return id, nil
}

// 11、删除t_did_conf配置信息
func DeleteDidConfById(o orm.Ormer, id int) (bool, error) {
	//根据主键id删除
	sql := "delete from t_did_conf where id = ?"
	_, err := o.Raw(sql, id).Exec()
	if err != nil {
		fmt.Println("DeleteDidConf err:", err)
		return false, err
	}
	fmt.Println("DeleteDidConf success")
	return true, nil
}
